﻿using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Modules.BankSpecialAccount.Entitys;
using Modules.System.User;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.Caching;
using PmSoft.DBContext;
using PmSoft.Entity;
using PmSoft.Repositories;
using PmSoft.Utilities;
using static Modules.BankSpecialAccount.BankSpecialAccountConstants;
using static Modules.Project.ProjectConstants;

namespace Modules.BankSpecialAccount.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class BankSpecialAccountRepository : Repository<BankSpecialAccountInfo, DefaultDbContext>
    {
        private readonly ILogger logger;
        private readonly IApplicationContext applicationContext;

        public BankSpecialAccountRepository(DefaultDbContext dbContext, IApplicationContext applicationContext, ILogger<BankSpecialAccountRepository> logger)
           : base(dbContext, applicationContext)
        {
            this.logger = logger;
            this.applicationContext = applicationContext;
        }

        /// <summary>
        /// author:hzj date:2023/11/8 describe:获取专户数据集合(分页)
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IPagedList<BankSpecialAccountInfo> GetProjectSpecialAccounts(BankSpecialAccountQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_special_account");
            sql.InnerJoin("project as b").On("project_special_account.project_id=b.project_id and b.is_deleted=0");

            #region 主管单位数据权限

            if (applicationContext.CurrentUser is IDeptUser deptUser && !deptUser.IsSuperAdministrator() && !deptUser.IsBankAdministrator())
            {
                sql.InnerJoin("project as prj").On($"project_special_account.project_id=prj.project_id and prj.is_deleted=0 and prj.industry_type=@0 ", deptUser.IndustryType);
                if (deptUser.Level == AdministrativeLevel.City)
                    sql.Append($" and prj.city_code={deptUser.CityCode}");
                if (deptUser.Level == AdministrativeLevel.Area)
                    sql.Append($" and prj.area_code={deptUser.AreaCode}");
            }

            #endregion 主管单位数据权限

            if (query.ProjectId.HasValue)
                sql.Where("project_special_account.project_id=@0", query.ProjectId);
            if (query.AccountStatus.HasValue)
            {
                sql.Where("account_status=@0", query.AccountStatus);
            }
            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("username like @0 or code like @0 or serial_number like @0 ", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            sql.OrderBy("project_special_account.create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        /// <summary>
        /// author:pingmac date:2024/2/29 describe:银行查询专户专用数据集合(分页)
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IPagedList<BankSpecialAccountInfo> GetReviewBankAccounts(SpecialAccountWithBankQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_special_account");

            if (!string.IsNullOrEmpty(query.AccBrook))
                sql.Where("code=@0", query.AccBrook);

            if (!string.IsNullOrEmpty(query.BankAccount))
                sql.Where("bank_no=@0", query.BankAccount);

            if (query.Status.HasValue)
                sql.Where("account_status=@0", query.Status);
            else
                sql.Where("account_status=@0 OR account_status=@1", 专户状态.正常, 专户状态.注销);

            if (!string.IsNullOrEmpty(query.BankCode))
                sql.Where("bank_type=@0", query.BankCode);

            if (query.StartDate.HasValue)
                sql.Where("account_create_time >=@0", query.StartDate.Value.Date);

            if (query.EndDate.HasValue)
                sql.Where("account_create_time<=@0", query.EndDate.Value.Date.AddDays(1));

            sql.OrderBy("project_special_account.create_time DESC");

            return GetPagingEntities(pageSize, pageIndex, sql);
        }


        /// <summary>
        /// 获取所有在建项目的正常专户
        /// </summary>
        /// <returns></returns>
        public IEnumerable<BankSpecialAccountInfo> GetAllNormalProjectSpecialAccounts()
        {
            Sql sql = Sql.Builder;
            sql.Select("a.id")
                .From("project_special_account as a");
            sql.InnerJoin("project as p").On("p.project_id=a.project_id");
            sql.Where("a.account_status=@0 and p.prj_status=@1", 专户状态.正常, 项目状态.在建);
            sql.OrderBy("a.create_time DESC");
            return PopulateEntitiesByEntityIds(DbContext.Fetch<int>(sql));
        }

        /// <summary>
        /// 通过projectId获取正常专户
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public BankSpecialAccountInfo? GetBankSpecialAccountByProjectId(int projectId)
        {
            return GetProjectSpecialAccountByProjectId(projectId).FirstOrDefault(i => i.AccountStatus == 专户状态.正常);
        }

        /// <summary>
        /// author:hzj date:2023/11/8 describe:根据projectId获取指定专户数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public IEnumerable<BankSpecialAccountInfo> GetProjectSpecialAccountByProjectId(int projectId)
        {
            int areaVersion = EntityCacheService.GetAreaVersion("ProjectId", projectId);
            return GetTopEntities(1000, CachingExpirationType.ObjectCollection, () =>
            {
                return $"ProjectSpecialAccountByProjectId:{projectId}:{areaVersion}";
            }, () =>
            {
                Sql sql = Sql.Builder
                .Select("id")
                .From("project_special_account")
                .Where("project_id=@0", projectId);
                return sql;
            });
        }

        /// <summary>
        /// author:hzj date:2023/11/8 describe:根据projectId获取指定专户数据行数
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public int GetProjectSpecialAccountCount(int projectId)
        {
            Sql sql = Sql.Builder;
            sql.Select("count(1)")
                .From("project_special_account");
            sql.Where("project_id=@0", projectId);
            return DbContext.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// author:hzj date:2024/2/4 describe:根据流水号获取指定专户数据
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public BankSpecialAccountInfo? GetProjectSpecialAccountByCode(string code)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_special_account");
            sql.Where("code=@0", code);
            return Get(DbContext.FirstOrDefault<int>(sql));
        }

        /// <summary>
        /// 根据专户账号获取专户ID
        /// </summary>
        /// <param name="bankAccount"></param>
        /// <returns></returns>
        public int? GetProjectSpecialAccountIdByBankNo(string bankAccount)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_special_account");
            sql.Where("bank_no=@0", bankAccount);
            return DbContext.FirstOrDefault<int?>(sql);
        }

        public BankSpecialAccountInfo? GetProjectSpecialAccountByMaxSerialNumber(DateTime time, int projectId)
        {
            Sql sql = Sql.Builder.Select("id").From("project_special_account");
            sql.Where("DATE(create_time)=@0", time.Date);
            sql.Where("project_id=@0", projectId);
            sql.OrderBy("serial_number desc");
            var id = DbContext.FirstOrDefault<int>(sql);
            if (id == 0)
                return null;
            return Get(id);
        }

        public IEnumerable<BankSpecialAccountInfo> GetProjectSpecialAccountByProjectIds(IEnumerable<int> projectIds)
        {
            if (projectIds == null || projectIds.Count() == 0)
                return Enumerable.Empty<BankSpecialAccountInfo>();

            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_special_account")
                .Where("project_id IN(@0)", projectIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }
    }
}